package cn.gson.crm.model.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.po.FollowPo;

public class FollowDao {
	private int bands = 8;
	/**
	 * 根据学生id查询所有的跟进记录
	 * */
	public List<FollowPo> getAllFollowPoList() throws SQLException {
		List<Map<String, Object>> listData = null;
		String sql = "SELECT follow.*, staff.staff_name ,student.student_name FROM follow "
				+ " LEFT OUTER JOIN staff ON staff.staff_id = follow.follow_staff_id "
				+ " LEFT OUTER JOIN student ON follow.follow_student_id = student.student_id"
				+ " ORDER BY follow.follow_time DESC;";
		listData = Jdbc.getInstace().executeQueryForList(sql);
		List<FollowPo> followPoList = null;
		if (listData != null && listData.size() > 0) {
			followPoList = buildFollowPo(listData);
		}
		return followPoList;
	}
	
	/**
	 * 根据条件查询分页跟进记录
	 * */
	public List<FollowPo> getAllFollowPoListByType(int page,String staffName, String studentName, String followRead) throws SQLException {
		List<Map<String, Object>> listData = null;
		String sql = "SELECT  staff.staff_name ,student.student_name,follow.* FROM follow "
				+ " LEFT OUTER JOIN staff ON staff.staff_id = follow.follow_staff_id "
				+ " LEFT OUTER JOIN student ON follow.follow_student_id = student.student_id";
		List<Object> params = new ArrayList<>();
		int start = (page - 1) * bands;
		sql = buildSqlAndParams(params, sql, staffName, studentName, followRead);
		sql += " ORDER BY follow.follow_time DESC LIMIT ?,?";
		params.add(start);
		params.add(bands);
		listData = Jdbc.getInstace().executeQueryForList(sql,params.toArray());
		List<FollowPo> followPoList = null;
		if (listData != null && listData.size() > 0) {
			followPoList = buildFollowPo(listData);
		}
		return followPoList;
	}
	
	/**
	 * 带条件获取跟进的分页总数
	 */
	public int getFollowPageSum(String staffName, String studentName, String followRead) throws SQLException {
		String sql = "SELECT COUNT(*) as sum FROM(SELECT  staff.staff_name ,student.student_name,follow.* FROM follow "
				+ " LEFT OUTER JOIN staff ON staff.staff_id = follow.follow_staff_id "
				+ " LEFT OUTER JOIN student ON follow.follow_student_id = student.student_id ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, staffName, studentName, followRead);
		sql += ") s";
		System.out.println(sql+ params.toArray());
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		int sum = 0;
		if (map != null) {
			sum = Integer.parseInt(map.get("sum").toString());
			sum = sum % bands > 0 ? sum / bands + 1 : sum / bands;
		}
		return sum;
	}
	
	
	/**
	 * 根据学生id查询所有的跟进记录
	 * */
	public List<FollowPo> getFollowPoList(String stuid) throws SQLException {
		List<Map<String, Object>> listData = null;
		String sql = "SELECT follow.*, staff.staff_name ,student.student_name FROM follow "
				+ " LEFT OUTER JOIN staff ON staff.staff_id = follow.follow_staff_id "
				+ " LEFT OUTER JOIN student ON follow.follow_student_id = student.student_id"
				+ " WHERE follow.follow_student_id = ? ORDER BY follow.follow_time DESC;";
		listData = Jdbc.getInstace().executeQueryForList(sql,stuid);
		List<FollowPo> followPoList = null;
		if (listData != null && listData.size() > 0) {
			followPoList = buildFollowPo(listData);
		}
		return followPoList;
	}
	/**
	 * 查询学生和咨询师的关系
	 * */
	public int getstuSta(String staid, String stuid) throws SQLException{
		String sql = "SELECT cooperation_id from cooperation where cooperation_staff_id = ? and cooperation_student_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, staid,stuid);
		int i = 0 ;
		if(map != null){
			i = type(map.get("cooperation_id"));
		}
		return i;
	}
	/**
	 * 根据跟进id查询跟进记录
	 * */
	public FollowPo getOneFollowPoList(String followid) throws SQLException {
		String sql = "SELECT follow.*, staff.staff_name ,student.student_name FROM follow "
				+ " LEFT OUTER JOIN staff ON staff.staff_id = follow.follow_staff_id "
				+ " LEFT OUTER JOIN student ON follow.follow_student_id = student.student_id"
				+ " WHERE follow.follow_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, followid);
		FollowPo followPo = new FollowPo();
		if(map!=null){
			followPo = buildOneFollowPo(map);
		}
		return followPo;
	}
	
	public boolean updateFollowContent(String followid,String followContent) throws SQLException{
		String sql = "update follow set follow_read = ?  where follow_id = ? ";
		return Jdbc.getInstace().executeUpdate(sql,followContent, followid);
	}

	
	/**
	 * 根据跟进id更新插入记录
	 * */
	public boolean updateFollowPoList(String stuid, String followid,String followStyle,String followAgainTime,String followContent) throws SQLException {
		String sql = "UPDATE follow SET follow_style=?  ,follow_content=? ,follow_again_time=? WHERE follow_id = ?";
		
		if(!StringUtils.isNullOrEmpty(followAgainTime)){
			updateDate(stuid, followAgainTime);
		}
		return Jdbc.getInstace().executeUpdate(sql, followStyle,followContent,followAgainTime,followid);
	}
	/**
	 * 根据跟进id插入跟进记录
	 * */
	public boolean insertFollowPoList(String stuid,String staffid,String followStyle,String followAgainTime,String followContent) throws SQLException {
		String sql = "insert into follow (follow_student_id,follow_staff_id, follow_content,follow_style,follow_again_time) VALUES (?,?,?,?,?)";
		if(!StringUtils.isNullOrEmpty(followAgainTime)){
			updateDate(stuid, followAgainTime);
		}
		return Jdbc.getInstace().executeUpdate(sql, stuid,staffid,followContent,followStyle,followAgainTime);
	}
	/**
	 * 根据跟进id更新插入记录
	 * */
	public boolean updateDate(String stuid,String followAgainTime) throws SQLException {
			String sql =  "update follow set follow_again_time = ? where follow_student_id = ? ";
			return Jdbc.getInstace().executeUpdate(sql, null,stuid);
			}
	protected FollowPo buildOneFollowPo(Map<String, Object> data){
			FollowPo followPo = new FollowPo();
			followPo.setFollowId(type(data.get("follow_id")));
			followPo.setFollowStaffId(type(data.get("follow_staff_id")));
			followPo.setFollowStudentId(type(data.get("follow_student_id")));
			followPo.setFollowStaffName((String)data.get("staff_name"));
			followPo.setFollowStudentName((String)data.get("student_name"));
			String s = data.get("follow_time").toString().split(" ")[0];
			followPo.setFollowTime(Date.valueOf(s));
			String as = null;
			if(data.get("follow_again_time")!=null){
				as = data.get("follow_again_time").toString().split(" ")[0];
			}
			//followPo.setFollowAgainTime(Date.valueOf(as));
			followPo.setFollowRead((String)data.get("follow_read"));
			followPo.setFollowStyle((String)data.get("follow_style"));
			followPo.setFollowContent((String)data.get("follow_content"));
	
		return followPo;
	}
	
	
	protected List<FollowPo> buildFollowPo(List<Map<String, Object>> listData){
		List<FollowPo> followPoList = new ArrayList<>();
		for (Map<String, Object> data : listData) {
			FollowPo followPo = new FollowPo();
			followPo.setFollowId(type(data.get("follow_id")));
			followPo.setFollowStaffId(type(data.get("follow_staff_id")));
			followPo.setFollowStudentId(type(data.get("follow_student_id")));
			followPo.setFollowStaffName((String)data.get("staff_name"));
			followPo.setFollowStudentName((String)data.get("student_name"));
			String s = data.get("follow_time").toString().split(" ")[0];
			followPo.setFollowTime(Date.valueOf(s));
			if(data.get("follow_again_time")!=null){
				String as = data.get("follow_again_time").toString().split(" ")[0];
				followPo.setFollowAgainTime(Date.valueOf(as));
			}
			followPo.setFollowRead((String)data.get("follow_read"));
			followPo.setFollowStyle((String)data.get("follow_style"));
			followPo.setFollowContent((String)data.get("follow_content"));
			followPoList.add(followPo);
		}
		return followPoList;
	}
	private String buildSqlAndParams(List<Object> params, String sql, String staffName, String studentName,
			String followRead) {
		if (!StringUtils.isNullOrEmpty(staffName) || !StringUtils.isNullOrEmpty(studentName)
				|| !StringUtils.isNullOrEmpty(followRead)) {
			sql += " where";
		}
		if (!StringUtils.isNullOrEmpty(staffName)) {
			sql += " staff.staff_name = ? ";
			params.add( staffName);
			if (!StringUtils.isNullOrEmpty(studentName)) {
				sql += " and ";
			}
		}

		if (!StringUtils.isNullOrEmpty(studentName)) {
			sql += " student.student_name = ?  ";
			params.add( studentName );
			if (!StringUtils.isNullOrEmpty(followRead)) {
				sql += " and ";
			}
		}
		if (!StringUtils.isNullOrEmpty(followRead)) {
			if("1".equals(followRead)){
				sql += " follow.follow_read is not NULL";
			}
			else{
				sql += " follow.follow_read is NULL";
			}
		}
		return sql;
	}
	/**
	 * 字符串转为int
	 * */
	protected int type(Object object) {
		return Integer.parseInt(object.toString());
	}
}
